This notebook gets the results from the DB to process into histograms and others

In [4]:

    

import pandas as pd
from sqlalchemy import create_engine
import plotly
import plotly.plotly as py
from plotly.graph_objs import *
#from plotly.offline import download_plotlyjs, init_notebook_mode, plot, iplot
plotly.tools.set_credentials_file(username='jdlara', api_key='24SbBJgKo2nHvFXZJ1i9')
#init_notebook_mode(connected=True)
#iplot([{"x": [1, 2, 3], "y": [3, 1, 6]}])


    

In [5]:

    

def connection(user,passwd,dbname, echo_i=False):
    str1 = ('postgresql+pg8000://' + user +':' + passw + '@switch-db2.erg.berkeley.edu:5433/' 
            + dbname + '?ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory')
    engine = create_engine(str1,echo=echo_i)
    return engine


    

In [6]:

    

user = 'jdlara'
passw = ''
dbname = 'apl_cec' 
engine= connection(user,passw,dbname)


    

In [7]:

    

query_histogram = ('with d_bm_stats as ('+
            'select min("D_BM_kg_sum")/1000 as min, ' +
                   'max("D_BM_kg_sum")/1000 as max ' +
              'from lemmav2.lemma_total where "D_BM_kg_sum" > 0) ' +
            'select width_bucket(("D_BM_kg_sum")/1000, min, max, 15) as bucket, ' +
                    'int4range(min(floor(("D_BM_kg_sum")/1000))::INT, max(("D_BM_kg_sum")/1000)::INT, \'[]\') as range, ' +
                    'count(*) as freq ' +
                'from lemmav2.lemma_total, d_bm_stats where "D_BM_kg_sum" > 100 ' +
            'group by bucket ' +
            'order by bucket;')
result = pd.read_sql_query(query_histogram,engine);


    

In [8]:

    

data = [
    Bar(
        x=result['range'], # assign x as the dataframe column 'x'
        y=result['freq']
    )
]
layout = Layout(
    yaxis=dict(
        title='Number of Pixels',
        type='log',
        autorange=True
    ),
    xaxis=dict(
        title='Biomass content range [Million Green Tons]',
        autorange=True
    )
)
fig = Figure(data=data, layout=layout)
py.iplot(fig, filename='Total BM accross years')


    

    
Out[8]:

In [16]:

    




    

In [ ]:

    




    

In [ ]:

    




    

In [ ]: